package cn.com.joco.dk.webby.generate;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.com.joco.dk.webby.excel.CellData;
import cn.com.joco.dk.webby.excel.RowData;

/**
 * EXCEL读写工具类
 * 约定：第一行为标题行，从第二行开始为真正的数据行
 */
public class XLSUtil {
	public static final String DATE_FORMATTER = "yyyy-MM-dd";
	private static final Logger logger = LoggerFactory.getLogger(XLSUtil.class);
	private static Workbook wb;

	public static void parse(InputStream is) {
		try {
			wb = WorkbookFactory.create(is);
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		}
	}

	public static void parse(String xlsFileName) {
		InputStream is;
		try {
			is = new FileInputStream(xlsFileName);
			wb = WorkbookFactory.create(is);
		} catch (FileNotFoundException e) {
			logger.error(e.getMessage(), e);
		} catch (IOException e) {
			logger.error(e.getMessage(), e);
		} catch (InvalidFormatException e) {
			logger.error(e.getMessage(), e);
		}
	}

	public static Workbook getBook() {
		return wb;
	}

	public static int getRowCount(int sheetIndex) {
		return wb.getSheetAt(sheetIndex).getLastRowNum();
	}

	/**
	 * 获取工作部的所有列标题
	 * 
	 * @param sheet
	 * @return
	 */
	public static List<CellData> getSheetCaption(int sheetIndex) {
		List<CellData> caption_list = new ArrayList<CellData>();
		Sheet sheet = wb.getSheetAt(sheetIndex);
		if (sheet == null) {
			return caption_list;
		}
		if (sheet.getLastRowNum() <= 0) {
			return caption_list;
		}
		Row caption_row = sheet.getRow(0);
		for (int i = 0; i < caption_row.getLastCellNum(); i++) {
			Cell item = caption_row.getCell(i);
			CellData cellData = new CellData(item.getColumnIndex(), getValue(item));
			caption_list.add(cellData);
		}
		return caption_list;
	}

	/**
	 * 获取excel表格中具体的表单中的某一行的所有列的数据
	 * 
	 * @param sheetIndex
	 * @param rowIndex
	 * @return
	 */
	public static List<String> getRowDataAt(int sheetIndex, int rowIndex) {
		List<String> rowDatas = new ArrayList<String>();
		Sheet sheet = wb.getSheetAt(sheetIndex);
		Row row = sheet.getRow(rowIndex);
		for (int i = 0; i < row.getLastCellNum(); i++) {
			Cell eachCell = row.getCell(i);
			String data = getValue(eachCell);
			rowDatas.add(data);
		}
		return rowDatas;
	}

	/**
	 * 获取前n行的数据
	 * 
	 * @param sheetIndex
	 * @param endRow
	 * @return
	 */
	public static List<RowData> getRowDatas(int sheetIndex, int endRow) {
		List<RowData> rowDatas = new ArrayList<RowData>();
		Sheet sheet = wb.getSheetAt(sheetIndex);
		for (int index = 1; index <= endRow; index++) {
			Row row = sheet.getRow(index);
			if (row == null) {
				// 解决客户导入不成功的错误
				continue;
			}
			RowData eachRowData = new RowData();
			eachRowData.setRowIndex(row.getRowNum());
			List<CellData> rowCellDatas = new ArrayList<CellData>();
			for (int i = 0; i < row.getLastCellNum(); i++) {
				Cell eachCell = row.getCell(i);
				int columIndex = 0;
				Object data = null;
				// 如果某个单元格没填值
				if (eachCell == null) {
					columIndex = i;
					data = "";
				} else {
					data = getCellString(eachCell);
					if (data == null) {
						data = "";
					}
					columIndex = eachCell.getColumnIndex();
				}
				CellData cellData = new CellData(columIndex, data);
				rowCellDatas.add(cellData);
			}
			eachRowData.setCells(rowCellDatas);
			rowDatas.add(eachRowData);
		}
		return rowDatas;
	}

	/**
	 * 获取excel表格中具体的表单中的某一行的某一列的数据
	 * 
	 * @param sheetIndex
	 * @param rowIndex
	 * @param columIndex
	 * @return
	 */
	public static String getCellData(int sheetIndex, int rowIndex, int columIndex) {
		Sheet sheet = wb.getSheetAt(sheetIndex);
		Row row = sheet.getRow(rowIndex);
		Cell cell = row.getCell(columIndex);
		return getValue(cell);
	}

	public static List getDatasInSheet(int sheetNumber) {
		List<List> result = new ArrayList<List>();
		// 获得指定的sheet
		Sheet sheet = wb.getSheetAt(sheetNumber);
		// 获得sheet总行数
		int rowCount = sheet.getLastRowNum();
		if (rowCount < 1) {
			return result;
		}

		// 遍历行row,约定第1行为标题行，第二行开始才是数据行
		for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++) {
			// 获得行对象
			Row row = sheet.getRow(rowIndex);
			if (null != row) {
				List<Object> rowData = new ArrayList<Object>();
				// 获得本行中单元格的个数
				int cellCount = row.getLastCellNum();
				// 遍历列cell
				for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {
					Cell cell = row.getCell(cellIndex);
					// 获得指定单元格中的数据
					Object cellStr = getCellString(cell);

					rowData.add(cellStr);
				}
				result.add(rowData);
			}
		}

		return result;
	}

	private static Object getCellString(Cell cell) {
		Object result = null;
		if (cell != null) {
			// 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
			int cellType = cell.getCellType();
			switch (cellType) {
			case Cell.CELL_TYPE_STRING:
				result = cell.getRichStringCellValue().getString();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					Date dateValue = cell.getDateCellValue();
					DateFormat dataFormat = new SimpleDateFormat(DATE_FORMATTER);
					result = dataFormat.format(dateValue);
				} else {
					result = cell.getNumericCellValue();
				}
				break;
			case Cell.CELL_TYPE_FORMULA:
				cell.setCellType(Cell.CELL_TYPE_STRING);
				result = cell.getStringCellValue();
				if (result != null) {
					result = ((String) result).replaceAll("#N/A", "").trim();
				}
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				result = cell.getBooleanCellValue();
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				break;
			case HSSFCell.CELL_TYPE_ERROR:
				break;
			default:
				break;
			}
		}
		return result;
	}

	/**
	 * 返回一个excel中sheet的数目
	 * 
	 * @return
	 */
	public static int getSheetCount() {
		return wb.getNumberOfSheets();
	}

	private static String getValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				Date dateValue = cell.getDateCellValue();
				DateFormat dataFormat = new SimpleDateFormat(DATE_FORMATTER);
				return dataFormat.format(dateValue);
			} else {
				return String.valueOf(cell.getNumericCellValue());
			}
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}

}
